﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Security;

public partial class Developer_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // SQL connection
        string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(connectionString);

        try
        {
            // open connection
            con.Open();
            SqlCommand cmd = new SqlCommand(
                "SELECT Medewerker.MDWID, Medewerker.UserName, Medewerker.AFDFUNID, AfdelingFunctie.AFDFUNID, AfdelingFunctie.FUNCID, Functie.FUNID, Functie.Naam, Security.Functie, Security.Rol " +
                "FROM Medewerker " +
                "INNER JOIN AfdelingFunctie " +
                "ON Medewerker.AFDFUNID = AfdelingFunctie.AFDFUNID " +
                "INNER JOIN Functie " +
                "ON AfdelingFunctie.FUNCID = Functie.FUNID " +
                "INNER JOIN Security " +
                "ON AfdelingFunctie.FUNCID = Security.Functie", con);

            // call execute reader to get query results
            SqlDataReader rdr = cmd.ExecuteReader();

            // create table
            Table tbl = new Table();
            Panel1.Controls.Add(tbl);

            while (rdr.Read())
            {
                // create table row
                TableRow tr = new TableRow();

                // create table cell
                TableCell tcUsername = new TableCell();
                TableCell tcFunctie = new TableCell();
                TableCell tcRol = new TableCell();

                // create label
                Label labelUsername = new Label();
                Label labelFunctie = new Label();
                Label labelRol = new Label();

                // add sql result in label
                labelUsername.Text = rdr.GetString(1);
                labelUsername.ID = "LabelUsername" + rdr.GetSqlInt32(0).ToString();
                labelFunctie.Text = rdr.GetString(6);
                labelFunctie.ID = "LabelFunctie" + rdr.GetSqlInt32(0).ToString();
                labelRol.Text = rdr.GetString(8);
                labelRol.ID = "LabelRol" + rdr.GetSqlInt32(0).ToString();

                // add label in table cell
                tcUsername.Controls.Add(labelUsername);
                tcFunctie.Controls.Add(labelFunctie);
                tcRol.Controls.Add(labelRol);

                // add table cell in table row
                tr.Cells.Add(tcUsername);
                tr.Cells.Add(tcFunctie);
                tr.Cells.Add(tcRol);

                // add table row in table
                tbl.Rows.Add(tr);

                // add user to ASPNETDB
                //Membership.CreateUser(rdr.GetString(1), rdr.GetString(1));

                // add user to role in ASPNETDB
                //Roles.AddUserToRole(rdr.GetString(1), rdr.GetString(8));
            }
        }
        finally
        {
            // close the connection
            if (con != null)
            {
                con.Close();
            }
        }

    }
}